-- drop columns
ALTER TABLE receivable DROP COLUMN "type";
ALTER TABLE receivable DROP COLUMN description;
ALTER TABLE receivable DROP COLUMN "value";
ALTER TABLE receivable DROP COLUMN is_draft;

-- add columns
ALTER TABLE receivable ADD COLUMN id_project integer;
ALTER TABLE receivable ADD COLUMN description character varying(511);
ALTER TABLE receivable ADD COLUMN remark character varying(511);
ALTER TABLE receivable ADD COLUMN receivable_type integer;
ALTER TABLE receivable ADD COLUMN money numeric;
ALTER TABLE receivable ADD COLUMN currency integer;

-- add constraints
ALTER TABLE receivable
  ADD CONSTRAINT receivable_id_project_fkey FOREIGN KEY (id_project)
      REFERENCES project (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE;

ALTER TABLE receivable
  ADD CONSTRAINT receivable_currency_fkey FOREIGN KEY (currency)
      REFERENCES currency (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE;
	  
--drop old SPs
DROP FUNCTION sp_receivable_count(integer, integer, integer, integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, character varying, character varying, character varying, numeric, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_receivable_insert(integer, integer, integer, timestamp without time zone, timestamp without time zone, character varying, character varying, character varying, numeric, integer, boolean, integer, integer);
DROP FUNCTION sp_receivable_selectlist(integer, integer, integer, integer, integer, integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, timestamp without time zone, character varying, character varying, character varying, numeric, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_receivable_update(integer, integer, integer, integer, timestamp without time zone, timestamp without time zone, character varying, character varying, character varying, numeric, integer, boolean, integer, integer, boolean, timestamp without time zone);

--add new SPs
CREATE OR REPLACE FUNCTION sp_receivable_insert(
	_id_company	integer DEFAULT null,
	_id_phase	integer DEFAULT null,
	_plan_date	timestamp without time zone DEFAULT null,
	_real_date	timestamp without time zone DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_creator	integer DEFAULT null,
	_id_project	integer DEFAULT null,
	_description	character varying(511) DEFAULT null,
	_remark	character varying(511) DEFAULT null,
	_receivable_type	integer DEFAULT null,
	_money	numeric DEFAULT null,
	_currency	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO receivable(id_company,id_phase,plan_date,real_date,name,code,status,notification,creator,updator,deletion_time,id_project,description,remark,receivable_type,money,currency)
	VALUES(_id_company,_id_phase,_plan_date,_real_date,_name,_code,_status,_notification,_creator,_creator,null,_id_project,_description,_remark,_receivable_type,_money,_currency);
RETURN currval('receivable_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_receivable_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_id_company	integer DEFAULT null,
	_id_phase	integer DEFAULT null,
	_plan_dateFrom	timestamp without time zone DEFAULT null,
	_plan_dateTo	timestamp without time zone DEFAULT null,
	_real_dateFrom	timestamp without time zone DEFAULT null,
	_real_dateTo	timestamp without time zone DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_id_project	integer DEFAULT null,
	_description	character varying(511) DEFAULT null,
	_remark	character varying(511) DEFAULT null,
	_receivable_type	integer DEFAULT null,
	_money	numeric DEFAULT null,
	_currency	integer DEFAULT null
)
RETURNS SETOF receivable
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM receivable WHERE
	 	(_id is null OR id = _id)	
	AND (_id_company is null OR id_company = _id_company)	
	AND (_id_phase is null OR id_phase = _id_phase)	
	AND (_plan_dateFrom is null OR plan_date >= _plan_dateFrom)
	AND	(_plan_dateTo is null OR plan_date <= _plan_dateTo)	
	AND (_real_dateFrom is null OR real_date >= _real_dateFrom)
	AND	(_real_dateTo is null OR real_date <= _real_dateTo)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_receivable_type is null OR receivable_type = _receivable_type)	
	AND (_money is null OR money = _money)	
	AND (_currency is null OR currency = _currency)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_receivable_count (
	_id	integer DEFAULT null,
	_id_company	integer DEFAULT null,
	_id_phase	integer DEFAULT null,
	_plan_dateFrom	timestamp without time zone DEFAULT null,
	_plan_dateTo	timestamp without time zone DEFAULT null,
	_real_dateFrom	timestamp without time zone DEFAULT null,
	_real_dateTo	timestamp without time zone DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_id_project	integer DEFAULT null,
	_description	character varying(511) DEFAULT null,
	_remark	character varying(511) DEFAULT null,
	_receivable_type	integer DEFAULT null,
	_money	numeric DEFAULT null,
	_currency	integer DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM receivable WHERE
	 	(_id is null OR id = _id)	
	AND (_id_company is null OR id_company = _id_company)	
	AND (_id_phase is null OR id_phase = _id_phase)	
	AND (_plan_dateFrom is null OR plan_date >= _plan_dateFrom)
	AND	(_plan_dateTo is null OR plan_date <= _plan_dateTo)	
	AND (_real_dateFrom is null OR real_date >= _real_dateFrom)
	AND	(_real_dateTo is null OR real_date <= _real_dateTo)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_receivable_type is null OR receivable_type = _receivable_type)	
	AND (_money is null OR money = _money)	
	AND (_currency is null OR currency = _currency);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_receivable_update 
(	_id integer,
	_id_company integer,
	_id_phase integer,
	_plan_date timestamp without time zone,
	_real_date timestamp without time zone,
	_name character varying(255),
	_code character varying(20),
	_status integer,
	_notification integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone,
	_id_project integer,
	_description character varying(511),
	_remark character varying(511),
	_receivable_type integer,
	_money numeric,
	_currency integer  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE receivable 
	SET 		id_company = _id_company,
		id_phase = _id_phase,
		plan_date = _plan_date,
		real_date = _real_date,
		name = _name,
		code = _code,
		status = _status,
		notification = _notification,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time,
		id_project = _id_project,
		description = _description,
		remark = _remark,
		receivable_type = _receivable_type,
		money = _money,
		currency = _currency
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
